/**
 * 清理孤立副将数据脚本
 * 
 * 功能：
 * 1. 查找副将表中没有玩家归属的副将
 * 2. 删除这些孤立的副将记录
 * 3. 同时清理相关的技能、装备、宝石等关联数据
 * 4. 生成清理报告
 */

const mysql = require('mysql2/promise');

// 数据库配置
const DB_CONFIG = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4',
    timezone: '+08:00'
};

class OrphanedDeputyCleaner {
    constructor() {
        this.connection = null;
        this.cleanupReport = {
            startTime: new Date().toISOString(),
            endTime: null,
            orphanedDeputies: [],
            deletedDeputies: 0,
            deletedSkills: 0,
            deletedEquipment: 0,
            deletedStones: 0,
            errors: [],
            summary: {}
        };
    }

    async connect() {
        try {
            this.connection = await mysql.createConnection(DB_CONFIG);
            console.log('✅ 数据库连接成功');
        } catch (error) {
            console.error('❌ 数据库连接失败:', error);
            throw error;
        }
    }

    async disconnect() {
        if (this.connection) {
            await this.connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }

    /**
     * 查找孤立的副将（没有玩家归属的副将）
     */
    async findOrphanedDeputies() {
        console.log('\n🔍 查找孤立的副将...');

        const query = `
            SELECT 
                dg.id,
                dg.base_id,
                dg.lvl,
                dg.star,
                dg.roleType,
                dg.created_at,
                dgb.name as deputy_name,
                dgb.star_level
            FROM deputy_general dg
            LEFT JOIN deputy_general_base dgb ON dg.base_id = dgb.id
            WHERE dg.id NOT IN (
                SELECT DISTINCT deputy_id FROM player_deputy WHERE deputy_id IS NOT NULL
                UNION
                SELECT DISTINCT deputy_id FROM player_deputy_warehouse WHERE deputy_id IS NOT NULL
            )
            ORDER BY dg.created_at ASC
        `;

        const [rows] = await this.connection.execute(query);

        console.log(`📋 发现 ${rows.length} 个孤立的副将:`);
        rows.forEach((deputy, index) => {
            console.log(`   ${index + 1}. ID: ${deputy.id}, 名称: ${deputy.deputy_name || '未知'}, 等级: ${deputy.lvl}, 星级: ${deputy.star}`);
        });

        this.cleanupReport.orphanedDeputies = rows;
        return rows;
    }

    /**
     * 获取副将的关联数据统计
     */
    async getDeputyRelatedData(deputyId) {
        const queries = {
            skills: `SELECT COUNT(*) as count FROM deputy_general_skill_learned WHERE deputy_id = ?`,
            equipment: `SELECT COUNT(*) as count FROM deputy_general_equipment WHERE deputy_id = ?`,
            stones: `SELECT COUNT(*) as count FROM deputy_general_stone WHERE deputy_id = ?`
        };

        const result = {};

        for (const [type, query] of Object.entries(queries)) {
            const [rows] = await this.connection.execute(query, [deputyId]);
            result[type] = rows[0].count;
        }

        return result;
    }

    /**
     * 删除副将的关联数据
     */
    async deleteDeputyRelatedData(deputyId) {
        const deletions = {
            skills: `DELETE FROM deputy_general_skill_learned WHERE deputy_id = ?`,
            equipment: `DELETE FROM deputy_general_equipment WHERE deputy_id = ?`,
            stones: `DELETE FROM deputy_general_stone WHERE deputy_id = ?`
        };

        const result = {};

        for (const [type, query] of Object.entries(deletions)) {
            const [resultSet] = await this.connection.execute(query, [deputyId]);
            result[type] = resultSet.affectedRows;
        }

        return result;
    }

    /**
     * 删除单个副将及其关联数据
     */
    async deleteDeputy(deputyId) {
        try {
            // 开始事务
            await this.connection.beginTransaction();

            // 1. 获取关联数据统计
            const relatedData = await this.getDeputyRelatedData(deputyId);

            // 2. 删除关联数据
            const deletedData = await this.deleteDeputyRelatedData(deputyId);

            // 3. 删除副将主记录
            const [resultSet] = await this.connection.execute(
                'DELETE FROM deputy_general WHERE id = ?',
                [deputyId]
            );

            // 提交事务
            await this.connection.commit();

            return {
                deputyDeleted: resultSet.affectedRows,
                skillsDeleted: deletedData.skills,
                equipmentDeleted: deletedData.equipment,
                stonesDeleted: deletedData.stones,
                relatedDataBefore: relatedData
            };

        } catch (error) {
            await this.connection.rollback();
            throw error;
        }
    }

    /**
     * 执行清理操作
     */
    async performCleanup() {
        console.log('\n🧹 开始清理孤立的副将数据...');

        const orphanedDeputies = this.cleanupReport.orphanedDeputies;

        if (orphanedDeputies.length === 0) {
            console.log('✅ 没有发现孤立的副将，无需清理');
            return;
        }

        for (const deputy of orphanedDeputies) {
            try {
                console.log(`\n🗑️ 删除副将 ID: ${deputy.id} (${deputy.deputy_name || '未知'})`);

                const result = await this.deleteDeputy(deputy.id);

                this.cleanupReport.deletedDeputies += result.deputyDeleted;
                this.cleanupReport.deletedSkills += result.skillsDeleted;
                this.cleanupReport.deletedEquipment += result.equipmentDeleted;
                this.cleanupReport.deletedStones += result.stonesDeleted;

                console.log(`   ✅ 副将删除成功`);
                console.log(`   📊 关联数据删除: 技能${result.skillsDeleted}个, 装备${result.equipmentDeleted}个, 宝石${result.stonesDeleted}个`);

            } catch (error) {
                console.error(`   ❌ 删除副将 ${deputy.id} 失败:`, error.message);
                this.cleanupReport.errors.push(`副将 ${deputy.id}: ${error.message}`);
            }
        }
    }

    /**
     * 验证清理结果
     */
    async verifyCleanup() {
        console.log('\n🔍 验证清理结果...');

        const query = `
            SELECT COUNT(*) as count
            FROM deputy_general dg
            WHERE dg.id NOT IN (
                SELECT DISTINCT deputy_id FROM player_deputy WHERE deputy_id IS NOT NULL
                UNION
                SELECT DISTINCT deputy_id FROM player_deputy_warehouse WHERE deputy_id IS NOT NULL
            )
        `;

        const [rows] = await this.connection.execute(query);
        const remainingOrphans = rows[0].count;

        if (remainingOrphans === 0) {
            console.log('✅ 验证通过：所有孤立的副将已清理完毕');
            return true;
        } else {
            console.log(`❌ 验证失败：仍有 ${remainingOrphans} 个孤立的副将`);
            return false;
        }
    }

    /**
     * 生成清理报告
     */
    generateReport() {
        this.cleanupReport.endTime = new Date().toISOString();

        // 计算统计信息
        this.cleanupReport.summary = {
            totalOrphanedFound: this.cleanupReport.orphanedDeputies.length,
            totalDeleted: this.cleanupReport.deletedDeputies,
            totalSkillsDeleted: this.cleanupReport.deletedSkills,
            totalEquipmentDeleted: this.cleanupReport.deletedEquipment,
            totalStonesDeleted: this.cleanupReport.deletedStones,
            errorCount: this.cleanupReport.errors.length,
            successRate: this.cleanupReport.orphanedDeputies.length > 0
                ? (this.cleanupReport.deletedDeputies / this.cleanupReport.orphanedDeputies.length * 100).toFixed(2) + '%'
                : '100%'
        };

        console.log('\n📊 清理报告:');
        console.log('='.repeat(50));
        console.log(`开始时间: ${this.cleanupReport.startTime}`);
        console.log(`结束时间: ${this.cleanupReport.endTime}`);
        console.log(`发现的孤立副将: ${this.cleanupReport.summary.totalOrphanedFound}`);
        console.log(`成功删除的副将: ${this.cleanupReport.summary.totalDeleted}`);
        console.log(`删除的技能记录: ${this.cleanupReport.summary.totalSkillsDeleted}`);
        console.log(`删除的装备记录: ${this.cleanupReport.summary.totalEquipmentDeleted}`);
        console.log(`删除的宝石记录: ${this.cleanupReport.summary.totalStonesDeleted}`);
        console.log(`错误数量: ${this.cleanupReport.summary.errorCount}`);
        console.log(`成功率: ${this.cleanupReport.summary.successRate}`);

        if (this.cleanupReport.errors.length > 0) {
            console.log('\n❌ 错误详情:');
            this.cleanupReport.errors.forEach((error, index) => {
                console.log(`   ${index + 1}. ${error}`);
            });
        }

        return this.cleanupReport;
    }

    /**
     * 执行完整的清理流程
     */
    async runCleanup() {
        try {
            console.log('🚀 开始清理孤立的副将数据...');

            // 1. 查找孤立的副将
            await this.findOrphanedDeputies();

            // 2. 执行清理
            await this.performCleanup();

            // 3. 验证清理结果
            const isValid = await this.verifyCleanup();

            // 4. 生成报告
            const report = this.generateReport();

            if (isValid) {
                console.log('\n🎉 清理完成！所有孤立的副将数据已删除');
            } else {
                console.log('\n⚠️ 清理完成，但验证时发现问题，请检查数据');
            }

            return report;

        } catch (error) {
            console.error('❌ 清理过程中发生错误:', error);
            this.cleanupReport.errors.push(`系统错误: ${error.message}`);
            return this.generateReport();
        }
    }
}

// 主函数
async function main() {
    const cleaner = new OrphanedDeputyCleaner();

    try {
        await cleaner.connect();
        const report = await cleaner.runCleanup();

        // 保存报告到文件
        const fs = require('fs');
        const reportFile = `orphaned-deputies-cleanup-report-${new Date().toISOString().replace(/[:.]/g, '-')}.json`;
        fs.writeFileSync(reportFile, JSON.stringify(report, null, 2));
        console.log(`\n📄 详细报告已保存到: ${reportFile}`);

    } catch (error) {
        console.error('❌ 清理失败:', error);
        process.exit(1);
    } finally {
        await cleaner.disconnect();
    }
}

// 如果直接运行此脚本
if (require.main === module) {
    main().catch(console.error);
}

module.exports = OrphanedDeputyCleaner;
